package com.dp.service.impl3;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;
import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pojo.ChannelVo;
import com.dp.util.DataMap;
import com.dp.vo.TAreainfo;

@Service
@Transactional
public class AreaChannelServiceImpl extends BaseImpl {
	
	/**
	 * 一级地区记录数
	 * @return
	 */
	public int getFirstLevelCount(){
		/*int ans = 0;
		try {
	        this.init();
			this.sql="select count(areaid) from t_areainfo where upareaid=0 and areaid!=0";
			rs1=stmt.executeQuery(sql);
			if(rs1.next()){
				ans = rs1.getInt(1);
			}
		 }catch (Exception e) {
				logger.error("getFirstLevelCount fail !!!" + e.getMessage());
				e.printStackTrace();
				if(conn!=null){
					try {
						conn.rollback();
					} catch (SQLException e1) {
						e1.printStackTrace();
					} 
				}
		} finally {
			this.closeAll();
		}
		return ans;*/
		
		
		int ans = 0;
		try {
			this.sql="select count(areaid) from t_areainfo where upareaid=0 and areaid!=0";
			ans=this.getOneInt();
		 }catch (Exception e) {
				logger.error("getFirstLevelCount fail !!!" + e.getMessage());
				e.printStackTrace();
		} 
		return ans;
		
		
	}	
	
	public List getListByParent(String parentId){
		/*List ans = new ArrayList();
		ChannelVo cv=null;
		try {
			this.init();
			String sql = "select areaid,areaname,upareaid from t_areainfo where upareaid= '"+parentId+"'";
			rs1=stmt.executeQuery(sql);
				sql="select areaid from t_areainfo where upareaid =?";
			prep=conn.prepareStatement(sql);
			while(rs1.next()){
				cv = new ChannelVo();
				cv.setChannelid(rs1.getString("areaid"));
				cv.setChannelName(rs1.getString("areaname"));
				cv.setPreviousId(rs1.getString("upareaid"));
				prep.setString(1, cv.getChannelid());
				rs2=prep.executeQuery();
				String isLeaf="0";
				if(rs2.next()){
					isLeaf="1";
				}
				cv.setIsLeaf(isLeaf);
				
				ans.add(cv);
			}
		}catch (Exception e) {
			logger.error("getFirstLevelCount fail !!!" + e.getMessage());
			e.printStackTrace();
			if(conn!=null){
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				} 
			}
		} finally {
			this.closeAll();
		}
		return ans;*/
		
		
		List ans = new ArrayList();
		ChannelVo cv=null;
		try {
			String sql = "select areaid,areaname,upareaid from t_areainfo where upareaid= '"+parentId+"'";
			List<Object[]> temp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:temp)
			{
				cv = new ChannelVo();
				cv.setChannelid(this.getString(o[0]));
				cv.setChannelName(this.getString(o[1]));
				cv.setPreviousId(this.getString(o[2]));
				String isLeaf="0";
				sql="select areaid from t_areainfo where upareaid ="+this.getString(o[0])+"";
				if(this.getOneInt()!=0){
					isLeaf="1";
				}
				cv.setIsLeaf(isLeaf);
				
				ans.add(cv);
			}
			
		}catch (Exception e) {
			logger.error("getFirstLevelCount fail !!!" + e.getMessage());
			e.printStackTrace();
		}
		return ans;
	}
	
	public List getList(int start,int end){
		/*List ans = new ArrayList();
		ChannelVo area=null;
		try {
			this.init();
			this.sql="select areaid,areaname from t_areainfo where upareaid=0 and areaid!=0" ;
			sql +=" limit "+start+" ,"+(end-start);
			prep=conn.prepareStatement(sql);
			rs1=prep.executeQuery();
			this.sql="SELECT T.areaid FROM t_areainfo T WHERE  T.upareaid =?";
			prep2=conn.prepareStatement(sql);
			while(rs1.next()){
				area=new ChannelVo();
				area.setChannelid(String.valueOf(rs1.getInt("areaid")));
				area.setChannelName(rs1.getString("areaname"));
				prep2.setString(1, area.getChannelid());
				rs2=prep2.executeQuery();
				String isLeaf="0";
				if(rs2.next()){
					isLeaf="1";
				}
				area.setIsLeaf(isLeaf);
				ans.add(area);
			}
		} catch (Exception e) {
			logger.error("getFirstLevelCount fail !!!" + e.getMessage());
			e.printStackTrace();
			if(conn!=null){
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				} 
			}
		} finally {
			this.closeAll();
		}
		return ans;*/
		
		List ans = new ArrayList();
		ChannelVo area=null;
		try {
			this.sql="select areaid,areaname from t_areainfo where upareaid=0 and areaid!=0" ;
			sql +=" limit "+start+" ,"+(end-start);
			List<Object[]> temp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:temp)
			{
				area=new ChannelVo();
				area.setChannelid(this.getString(o[0]));
				area.setChannelName(this.getString(o[1]));
				String isLeaf="0";
				this.sql="SELECT T.areaid FROM t_areainfo T WHERE  T.upareaid ="+this.getString(o[0])+"";
				if(this.getOneInt()!=0){
					isLeaf="1";
				}
				area.setIsLeaf(isLeaf);
				ans.add(area);
			}
		} catch (Exception e) {
			logger.error("getFirstLevelCount fail !!!" + e.getMessage());
			e.printStackTrace();
		}
		return ans;
	}
	

	/**
	 * 取得起始行和终止行.
	 * @param request 请求
	 * @param allCount 总行数
	 * @param pageSize 每页数目
	 * @return 整型数组
	 */
	public static int[] getStartAndEndInfo(HttpServletRequest request,
			int allCount, int pageSize) {
		//默认当前的页数是1
		int page = 1;
		//默认的分页行数就是传入的每页行数
		int limit = pageSize;
		//新的起始位置
		int newStart = 0;
		int newEnd = 0;
		//在点击翻页按钮的时候,会传递gtstart参数到后台.
		if (request.getParameter("gtpage") != null) {
			limit = Integer.parseInt(request.getParameter("gtlimit"));
			page = Integer.parseInt(request.getParameter("gtpage"));
		}
		//否则就说明是第一次进入页面进行的查询
		else {
			if (request.getParameter("gtlimit") != null)
				limit = Integer.parseInt(request.getParameter("gtlimit"));
		}
		request.setAttribute("gtlimit", limit);
		request.setAttribute("gtpage", page);
		request.setAttribute("gtcount", allCount);

		//如果是第一次显示,而且要显示分页栏,就说明只显示第一页之间的数据
		if (limit != -1) {
			return new int[] {
					(page - 1) * limit,
					(page * limit + 1) > allCount + 1 ? allCount + 1 : (page
							* limit) };
		}
		//说明是第一次显示页面,而且不用显示分页栏,就是要返回全部的数据.
		else {
			return new int[] { 0, allCount };
		}
	}

	public ArrayList<DataMap> findAllAreaChannel(String rootch) {
		String findSql = "select ch.channel_id,ch.channel_name from t_channels ch,news_extends_firstchannel nef where ch.primal_id =(select channel_id from t_channels where channel_name='news' and status='ENABLED')  and ch.previous_id =nef.channel_id and nef.channelid='"+rootch+"' ";
		try {
			return this.queryALLResultSet(findSql.toString());
		} catch (Exception e) {
			logger.error(" findAllAreaChannel fail !!!" +e.getMessage());
			return null;
		}
	}
	
	public ArrayList<DataMap> findSelectChannelsByAeaid(Integer areaid,int chtype)
	{
		String findSql = "select tc.channel_id,tc.channel_name,ta.ch_order from t_channels tc,t_area_channel ta where tc.channel_id=ta.channel_id and areaid="+areaid+" and chtype="+chtype+" order by ch_order";
		try {
			return this.queryALLResultSet(findSql.toString());
		} catch (Exception e) {
			logger.error(" findSelectChannelsByAeaid fail !!!" +e.getMessage());
			return null;
		}
	}
	
	public boolean relationChannels(TAreainfo areainfo, String channel_ids,int chtype) {
		/*boolean resultb=true;
		try {
			this.init();
			this.sql="delete from t_area_channel where areaid=? and chtype=?";
			this.prep=this.conn.prepareStatement(sql);	
			this.sql="insert into t_area_channel (areaid,channel_id,ch_order,chtype) values (?,?,?,?)";
			this.prep1=this.conn.prepareStatement(sql);
			//删除老的地区和栏目的关系
			this.prep.setInt(1, areainfo.getAreaid());
			this.prep.setInt(2, chtype);
			prep.executeUpdate();
			
			if(channel_ids!=null&&!"".equals(channel_ids)){
				String[] chid_arys=channel_ids.split(",");
				//增加新的地区和栏目的关系
				for (int i = 0; i < chid_arys.length; i++) {
					String chid = chid_arys [i] ;
					prep1.setInt(1, areainfo.getAreaid());
					prep1.setString(2, chid);
					prep1.setInt(3, i+1);
					prep1.setInt(4, chtype);
					prep1.addBatch();
					}
				prep1.executeBatch();
			}
			
			conn.commit(); 
		} catch (Exception e) {
			logger.info("relationChannels fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
			if(conn!=null){
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				} 
			}
		} finally {
			this.closeAll();
		}
		return resultb;*/
		
		boolean resultb=true;
		try {
			//删除老的地区和栏目的关系
			this.sql="delete from t_area_channel where areaid=? and chtype=?";
			Query query=em.createNativeQuery(sql);
			query.setParameter(1, areainfo.getAreaid());
			query.setParameter(2, chtype);
			query.executeUpdate();
			
			if(channel_ids!=null&&!"".equals(channel_ids)){
				String[] chid_arys=channel_ids.split(",");
				//增加新的地区和栏目的关系
				this.sql="insert into t_area_channel (areaid,channel_id,ch_order,chtype) values (?,?,?,?)";
				query=em.createNativeQuery(sql);
				for (int i = 0; i < chid_arys.length; i++) {
					String chid = chid_arys [i] ;
					query.setParameter(1, areainfo.getAreaid());
					query.setParameter(2, chid);
					query.setParameter(3, i+1);
					query.setParameter(4, chtype);
					query.executeUpdate();
				}
			}
			
		} catch (Exception e) {
			logger.info("relationChannels fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
	}
	
	public boolean sortChannels(TAreainfo areainfo, String channel_ids,int chtype) {
		/*boolean resultb=true;
		try {
			this.init();
			this.sql="update t_area_channel set  ch_order=? where channel_id=? and areaid=? and chtype=?";
			this.prep1=this.conn.prepareStatement(sql);
			if(channel_ids!=null&&!"".equals(channel_ids)){
				String[] chid_arys=channel_ids.split(",");
				//更新地区与栏目关系的顺序
				for (int i = 0; i < chid_arys.length; i++) {
					String chid = chid_arys [i] ;
					prep1.setInt(1, i+1);
					prep1.setString(2, chid);
					prep1.setInt(3, areainfo.getAreaid());
					prep1.setInt(4, chtype);
					prep1.addBatch();
					}
				prep1.executeBatch();
			}
			
			conn.commit(); 
		} catch (Exception e) {
			logger.info("relationChannels fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
			if(conn!=null){
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				} 
			}
		} finally {
			this.closeAll();
		}
		return resultb;*/
		
		
		boolean resultb=true;
		try {
			this.sql="update t_area_channel set  ch_order=? where channel_id=? and areaid=? and chtype=?";
			Query query=em.createNativeQuery(sql);
			if(channel_ids!=null&&!"".equals(channel_ids)){
				String[] chid_arys=channel_ids.split(",");
				//更新地区与栏目关系的顺序
				for (int i = 0; i < chid_arys.length; i++) {
					String chid = chid_arys [i] ;
					query.setParameter(1, i+1);
					query.setParameter(2, chid);
					query.setParameter(3, areainfo.getAreaid());
					query.setParameter(4, chtype);
					query.executeUpdate();
				}
			}
		} catch (Exception e) {
			logger.info("relationChannels fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		} 
		return resultb;
		
	}
}
